#删除重复的古诗词数据
#腾讯来源内根据 md5去重
import datetime
import pymysql
import requests

from dbutils.pooled_db import PooledDB


# 测试库
def getBetaConnection():
    pool = PooledDB(pymysql, 1, host='172.20.150.110', user='test_movie', passwd='movie20189933',
                db='cc_media_source', port=4308)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 正式库
def getProdConnection():
    pool = PooledDB(pymysql, 1, host='193.112.206.244', user='developer_read', passwd='developer_read#coocaa',
                    db='cc_media_source', port=3306)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


#删除腾讯来源重复的古诗文
def delRepeatPoetry():
    conn, cur = getProdConnection()
    try:
        # 获取总记录数
        total_count_sql = "SELECT COUNT(*) FROM (select md5,count(1) from cc_ancient_poetry t where 1=1 group by t.md5 having count(1)>1 ) tt"
        cur.execute(total_count_sql)
        total_count = cur.fetchone()[0]

        batch_size = 100
        offset = 0

        while offset < total_count:
            # 使用LIMIT和OFFSET进行分页查询
            select_sql = f"""
                select md5,count(1) from cc_ancient_poetry t where 1=1 group by t.md5  having count(1)>1  order by md5 desc
                LIMIT {batch_size} OFFSET {offset}
                """
            cur.execute(select_sql)
            results = cur.fetchall()

            if not results:
                # 如果没有更多数据，则退出循环
                break
            ids = []
            for result in results:
                md5 = result[0]
                select_sql_md5 = f"""
                                SELECT t.id FROM cc_ancient_poetry t WHERE t.source = 'tencent' and  t.md5 = '{md5}'
                              AND t.id NOT IN ( SELECT min(id) FROM cc_ancient_poetry
                                WHERE  source = 'tencent' and  md5 = '{md5}');
                                """
                cur.execute(select_sql_md5)
                results_md5 = cur.fetchall()
                for result in results_md5:
                    id =result[0]
                    ids.append(id)
                #print(f"md5为:{md5}")
            deleteApi(ids)
            print(f"第{offset}序号的数据进行保存")
            # 删除这批数据到API

            # 更新offset以进行下一次查询
            offset += batch_size

    finally:
        # 确保无论如何都关闭数据库连接
        cur.close()
        conn.close()

#删除指定的范围的古诗文
def deleteByNumber():
    # 定义起始和结束ID
    start_id = 257129
    end_id = 315619

    # 定义每批删除的ID数量
    batch_size = 100

    # 定义删除接口的URL（不含查询参数）
    delete_url = "http://106.55.8.90:7001/api/ancientPoetry/deletePoetry/invoke"

    # 生成ID列表
    id_list = list(range(start_id, end_id + 1))

    # 分割ID列表为多个批次
    batches = [id_list[i:i + batch_size] for i in range(0, len(id_list), batch_size)]

    # 对每一批ID调用删除函数
    for ids in batches:
        deleteApi(ids)


def deleteApi(ids):
    # 定义删除接口的URL（不含查询参数）
    delete_url = "http://106.55.8.90:7001/api/ancientPoetry/deletePoetry/invoke"
    #delete_url = "http://localhost:8080/api/ancientPoetry/deletePoetry/invoke"
    # 构造查询参数
    params = {'ids': ','.join(map(str, ids))}
    # 发送HTTP GET请求
    response = requests.get(delete_url, params=params)
    # 检查响应状态码
    if response.status_code == 200:
        print(f"Successfully deleted IDs,删除数量:{len(ids)}")
    else:
        print(f"Failed to delete IDs: {','.join(map(str, ids))}, Status Code: {response.status_code}, Response: {response.text}")

if __name__ == '__main__':
    #delRepeatPoetry();
    delRepeatPoetry()